/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.compomics.ms1_intensitiesretrieval;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import org.apache.log4j.Level;
import org.apache.log4j.Logger;

/**
 *
 * @author Jonas
 */
public class DataProvider {

    //Define the query String that is going to be executed in ms_lims
    private static final String QUERY = "select identification.modified_sequence as sequence, identification.precursor as precursorMZ, scan.rtsec as rtSec, spectrum.total_spectrum_intensity as totSpecInt"
            + " from identification, scan, spectrum"
            + " where l_projectid = ? and spectrum.spectrumid = scan.l_spectrumid and identification.l_spectrumid = spectrum.spectrumid"
            + " and substring(spectrum.filename,2,4) = ? order by precursorMZ";
    
    private static final Logger LOGGER = Logger.getLogger(DataProvider.class);
    
    JDBCConnection jDBCConnection = new JDBCConnection("enter_username", "enter_user_password", "jdbc:mysql://muppet03.ugent.be/projects");

    // Query ms_lims and store every identification in a list
    public List<MSLimsHits> querySpectrumVariables(int projectID, String runNumber) {
        
        List<MSLimsHits> identificationsList = new ArrayList<MSLimsHits>();
        
        try {
            Connection connection = jDBCConnection.getDatabaseConnection();
            PreparedStatement stmt = connection.prepareStatement(QUERY);
            stmt.setInt(1, projectID);
            stmt.setString(2, runNumber);

            ResultSet rs = stmt.executeQuery();
            LOGGER.setLevel(Level.ALL);
            LOGGER.info("Starting query...");
            while (rs.next()) {
                double prec = rs.getDouble("precursorMZ");
                double rtSec = rs.getDouble("rtSec");
                double totSpecInt = rs.getDouble("totSpecInt");
                
                MSLimsHits identification = new MSLimsHits(prec, rtSec, totSpecInt);
                identificationsList.add(identification);               
            }
            stmt.close();
            connection.close();
        } catch (SQLException e) {
            LOGGER.error(e.getMessage());
        }
        LOGGER.info("Query finished!");
        return identificationsList;

    }
}
